﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using IHISCETeim.Library;


namespace IHISCETeim.Web.Export.FTradeModel
{
    public partial class FTradeExportHKTSH : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "应收帐款_财务部还款提示函.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "还款提示函";
            }
        }

        protected string DateFrom
        {
            get
            {
                DateTime dt = DateTime.Now; //当前时间
                return GetRequestValue<string>("DateFrom", DateTime.Now.ToString("yyyy-MM-01"));
            }
        }
        protected string DateTo
        {
            get
            {
                DateTime dt = Convert.ToDateTime( DateTime.Now.ToString("yyyy-MM-01")); //当前时间

                return GetRequestValue<string>("DateTo", dt.ToString("yyyy-MM-dd 23:59:59"));

            }
        }
        /// <summary>
        /// 当前日期
        /// </summary>
        protected string CurrDate
        {
            get
            {
                return DateTime.Now.ToString("yyyy年MM月dd日");
            }
        }
        protected string GetSQLWhere()
        {
            //营业经理权限 2014-02-21
            string agentList = base.FTradeAgentPowerSqlWhere(UserId);
            string wheresql = string.IsNullOrEmpty(agentList) ? " 1=1 " : string.Format(" JingXiaoShang in ({0})", agentList);

            wheresql += " and WeiShouKuanXiang>0";

            return wheresql;
        }
        protected void Page_Load(object sender, EventArgs e)
        {
           // throw new Exception("xx");
            //所有有未收款项的经销商
            DataTable allAgentlist = FTradeReportController.GetALLJinXiaoShang(GetSQLWhere());
           
            string dateto = DateTo;//应付款最后到期日
            DataTable allDetail = FTradeReportController.FTradeExportGetHKTSHDetail("",dateto);//所有到期应还款
            DataTable sboagentlist = SBOController.GetAgentList();//所有经销商列表
            //
            InitializeWorkbook();

            string AliasName = "";
            int sheetindex =1;//从第二个工作表开始
            foreach (DataRow dr in allAgentlist.Rows)
            {
                AliasName = dr["JingXiaoShang"].ToString();
                DataTable detaildt = new DataTable();

                switch (AliasName)
                { 
                    case "湖南金力源":
                        detaildt = allDetail.Select("JingXiaoShang in('" + AliasName + "','湖南金土地')").CopyToDataTable();
                        break;
                    case "青岛金吉宇":
                        detaildt = allDetail.Select("JingXiaoShang in('" + AliasName + "','青岛吉宇')").CopyToDataTable();
                        break;
                    default:
                        detaildt = allDetail.Select("JingXiaoShang='" + AliasName + "'").CopyToDataTable();
                        break;
                }
               
                //if (AliasName == "湖南金力源")
                //{
                //    detaildt = allDetail.Select("JingXiaoShang in('" + AliasName + "','湖南金土地')").CopyToDataTable();
                //}
                //else
                //{
                //    detaildt = allDetail.Select("JingXiaoShang='" + AliasName + "'").CopyToDataTable();
                //}
                ISheet sheet2 = hssfworkbook.CloneSheet(0);
                hssfworkbook.SetSheetName(sheetindex, AliasName);
                //经销商全称                
                DataRow[] drs = sboagentlist.Select(string.Format(" AliasName ='{0}'",AliasName.Trim()));
                string cardname = drs[0]["CardName"].ToString();

                if (detaildt.Rows.Count <= 0)
                    continue;

                WriteSheet(sheet2, detaildt, cardname);

                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }
            //隐藏
            hssfworkbook.RemoveSheetAt(0);
            //
            WriteToFile();
        }

        private void WriteSheet(ISheet sheet2, DataTable exportDT, string cardname)
        {
            
            //设置文号（NO.）：
            sheet2.GetRow(8).GetCell(5).SetCellValue(string.Format("FG{0}-01",DateTime.Now.ToString("yyyyMMdd")));
            //
            sheet2.GetRow(5).GetCell(1).SetCellValue(cardname);
            //第12行 处理
            //截止2013年07月09日,贵司整机欠款总额为6.0512万元(明细见附表),其中本月应还款总额为6.0512万元,提醒贵司到期按时还款,以免影响信用评估。
            string WeiShouKuanXiang= Convert.ToDecimal(exportDT.Compute("SUM(WeiShouKuanXiang)", "")).ToString("N6");
            string daoqijine =( Convert.ToDecimal(exportDT.Compute("SUM(daoqijine)", "")) + Convert.ToDecimal(exportDT.Compute("SUM(currMoneyJinE)", ""))).ToString("N6"); 
            //
            ICell richTextCell= sheet2.GetRow(11).GetCell(0);
            string richText = string.Format("       截止{0},贵司整机欠款总额为{1}万元(明细见附表).本月应还款总额为{2}万元,提醒贵司到期按时还款,以免影响信用评估", CurrDate, WeiShouKuanXiang, daoqijine);
            HSSFRichTextString richtextObj = new HSSFRichTextString(richText);
            IFont fontStyleRich = hssfworkbook.CreateFont();
            fontStyleRich.Underline = (byte)FontUnderlineType.SINGLE;
            fontStyleRich.Boldweight = short.MaxValue;//加粗
            fontStyleRich.FontHeightInPoints = 12;
            //12号字体样式
            IFont fontStyleRich2 = hssfworkbook.CreateFont();
            fontStyleRich2.FontHeightInPoints = 12;

            richtextObj.ApplyFont(0, richText.Length, fontStyleRich2);
            //1.加粗位置处理
            int boldStart = string.Format("       截止{0},贵司整机欠款总额为", CurrDate).Length; ;
            int boldEnd = string.Format("       截止{0},贵司整机欠款总额为{1}万元", CurrDate, WeiShouKuanXiang).Length;
            richtextObj.ApplyFont(boldStart, boldEnd, fontStyleRich);
            boldStart = string.Format("       截止{0},贵司整机欠款总额为{1}万元(明细见附表),", CurrDate, WeiShouKuanXiang).Length;
            boldEnd = string.Format("       截止{0},贵司整机欠款总额为{1}万元(明细见附表).本月应还款总额为{2}万元,提醒贵司到期按时还款", CurrDate, WeiShouKuanXiang, daoqijine).Length;
            richtextObj.ApplyFont(boldStart, boldEnd, fontStyleRich);           
            richTextCell.SetCellValue(richtextObj);
            //HSSFRichTextString
            //STRING:附表：整机欠款明细（截止2013年07月09日）：
            richText = string.Format("附表：整机欠款明细（截止{0}）",CurrDate);
            //ICellStyle style12 = hssfworkbook.CreateCellStyle();
            //style12.SetFont(fontStyleRich2);
            ICell cell1 = sheet2.GetRow(12).GetCell(0); ;
            //cell1.CellStyle = style12;
            cell1.SetCellValue(richText);
            //列标题
            //sheet2.GetRow(13).GetCell(3).SetCellValue(string.Format("贵司{0}月份应还款",Convert.ToDateTime(DateTo).ToString("MM")));
            //设置其它信息
            sheet2.GetRow(20).GetCell(4).SetCellValue(CurrDate);
            sheet2.GetRow(21).GetCell(0).SetCellValue(string.Format("-------------------------请填下表于{0}月25日前回传：0592-6292200----------------------", Convert.ToDateTime(DateTo).ToString("MM")));
            sheet2.GetRow(22).GetCell(0).SetCellValue(string.Format("{0}份还款计划回复", Convert.ToDateTime(DateTo).ToString("yyyy年MM月")));
            sheet2.GetRow(27).GetCell(4).SetCellValue(string.Format("预计付款日：  {0}月   日", Convert.ToDateTime(DateTo).ToString("MM")));
            sheet2.GetRow(29).GetCell(0).SetCellValue(string.Format("{0}（盖章）", cardname));
            sheet2.GetRow(30).GetCell(4).SetCellValue(string.Format("{0}   日", Convert.ToDateTime(DateTo).ToString("yyyy年MM月")));
            //开始FOR各合同信息
            if (exportDT.Rows.Count > 2)
            {
                HSSFRow 源格式行 = (HSSFRow)sheet2.GetRow(14);
                MyInsertRow((HSSFSheet)sheet2, 15, exportDT.Rows.Count - 2, 源格式行);
            }
            int rowindexBase = 14;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            ICellStyle style12 = hssfworkbook.CreateCellStyle();
            style12.SetFont(fontStyleRich2);
            //水平对齐  
            style12.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
            //垂直对齐  
            style12.VerticalAlignment = VerticalAlignment.CENTER;
            //自动换行  
            style12.WrapText = true;

            foreach (DataRow dr in exportDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet2.GetRow(rowReal);
                //合并处理
              //  sheet2.AddMergedRegion(new CellRangeAddress(rowReal, rowReal, 4, 5));
               // row1.GetCell(4).CellStyle = style12;

                row1.GetCell(0).SetCellValue(dr["HeTongHao"].ToString());
                row1.GetCell(1).SetCellValue(dr["JiHao"].ToString());
                row1.GetCell(2).SetCellValue(Convert.ToDouble(dr["HeTongJinE"].ToString()));
                row1.GetCell(3).SetCellValue(Convert.ToDouble(dr["WeiShouKuanXiang"].ToString()));
                row1.GetCell(4).SetCellValue(Convert.ToDouble(dr["feiqi"].ToString()));
                row1.GetCell(5).SetCellValue(Convert.ToDouble(dr["yibanjine"].ToString()) - Convert.ToDouble(dr["yibankuchujine"].ToString()));
                row1.GetCell(6).SetCellValue(Convert.ToDouble(dr["yibankuchujine"].ToString()));
                row1.GetCell(7).SetCellValue( dr["FuKuanTiaoKuan"].ToString());
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

        }

       


        protected override string GetXlsFileName(string oldFileName)
        {
            //return base.GetXlsFileName(oldFileName);

            return string.Format("{0}{1}.xls",  DateTime.Now.ToString("yyyy年MM月"),"应收帐款还款提示函汇总表").Trim();
        }
    }
}